using ClosedXML.Excel;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.IO;
namespace printorder
    {
// 数据库查询类
class DatabaseQuery
{
    public static (List<string[]>, List<string[]>, string) QueryData()
    {
        List<string[]> list1 = new List<string[]>();
        List<string[]> list2 = new List<string[]>();
        string firstRemark = "";

        try
        {
            // 读取 dingdan.txt 文件中的订单号
            string[] orderNumbers = File.ReadAllLines("./param/dingdan.txt");


            // 定义连接字符串
            string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.142)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User Id=lc001JGJ;Password=JGJ202503;";

            // 创建 Oracle 连接对象
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                // 打开数据库连接
                connection.Open();

                // 构建 SQL 查询语句，使用 IN 子句
                string query = @"
                    select 
                        lc0019999.V_SalesOrders_JGJ.""danjuxinxi"" as ""订单号"",
                        lc0019999.V_SalesOrders_JGJ.""xiangmumingcheng"" as ""项目名称"",
                        lc0019999.V_SalesOrders_JGJ.""yewuriqi"" as ""业务日期"",
                        lc0019999.V_SalesOrders_JGJ.""xiaoshoubumen"" as ""业务部门"",
                        lc0019999.V_SalesOrders_JGJ.""yewuyuan"" as ""业务员"",
                        lc0019999.V_SalesOrders_JGJ.""jiaohuoriqi"" as ""发货日期"",
                        lc0019999.V_SalesOrders_JGJ.""shoudafang"" as ""需方单位"",
                        lc0019999.V_SalesOrders_JGJ.""waibuhetonghao"" as ""外部合同号"",
                        lc0019999.V_SalesOrders_JGJ.""neibuhetonghao"" as ""内部合同号"",
                        null as ""标准号"",
                        lc0019999.V_SalesOrders_JGJ.""wuliaomingcheng"" as ""产品名称"",
                        lc0019999.V_SalesOrders_JGJ.""dinghuoshuliang"" as ""数量"",
                        lc0019999.V_SalesOrders_JGJ.""hanshuidanjia"" as ""含税单价"",
                        lc0019999.V_SalesOrders_JGJ.""hanshuijine"" as ""含税金额"",
                        lc0019999.V_SalesOrders_JGJ.""xiangmumingcheng"" as ""项目名称"",
                        lc0019999.V_SalesOrders_JGJ.""picihao"" as ""批次"",
                        lc0019999.V_SalesOrders_JGJ.""xiaoshoubeizhu"" as ""备注"",
                        null as ""包装""
                    from 
                        lc0019999.V_SalesOrders_JGJ 
                    where 
                        lc0019999.V_SalesOrders_JGJ.""danjuxinxi"" IN ({0})";

                string orderNumberPlaceholders = string.Join(",", orderNumbers.Select(n => $"'{n}'"));
                query = string.Format(query, orderNumberPlaceholders);

                // 创建 Oracle 命令对象
                using (OracleCommand command = new OracleCommand(query, connection))
                {
                    // 执行查询并获取数据读取器
                    using (OracleDataReader reader = command.ExecuteReader())
                    {
                        // 遍历数据读取器中的每一行
                        while (reader.Read())
                        {
                            string[] row1 = new string[9];
                            string[] row2 = new string[reader.FieldCount - 9];

                            for (int i = 0; i < 9; i++)
                            {
                                row1[i] = reader[i].ToString();
                            }

                            for (int i = 10; i < reader.FieldCount; i++)
                            {
                                row2[i - 9] = reader[i].ToString();
                            }

                            if (list1.Count == 0)
                            {
                                firstRemark = reader["备注"].ToString();
                                if (firstRemark.Length == 0)
                                {
                                    firstRemark = "常规打包 √ ";
                                } else
                                {
                                    firstRemark = "特殊打包 √ ：" + firstRemark;
                                }
                            }

                            list1.Add(row1);
                            list2.Add(row2);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            // 捕获并输出异常信息
            Console.WriteLine("发生错误: " + ex.Message);
        }
        return (list1, list2, firstRemark);
    }
}

// 打印联系单类
class ContactSheetGenerator
{
    private XLWorkbook workbook;
    private IXLWorksheet worksheet;
    private int currentRow = 1;

    public ContactSheetGenerator()
    {
        workbook = new XLWorkbook();
        worksheet = workbook.Worksheets.Add("Sheet1");
    }

    // 生成联系单头部信息并填充数据
    private void GenerateHeader(string[] headerData, string packingRequirement)
    {
        // 设置第一列宽度为 20
        worksheet.Column(1).Width = 12;
        worksheet.Column(9).Width = 10;

        // 填充 1 - AJ 单元格（实际是 A 到 J 列）
        var range1 = worksheet.Range($"A{currentRow}:I{currentRow}");
        range1.Merge().Value = "安徽长江紧固件有限责任公司";
        range1.Style.Font.Bold = true;
        range1.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        range1.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        range1.Style.Font.FontSize += 5;
        range1.Style.Alignment.WrapText = true;
        range1.Style.Alignment.SetIndent(0); // 设置缩进为 0
        currentRow++;

        // 填充 2 - AH 单元格（实际是 A 到 H 列）
        var range2 = worksheet.Range($"A{currentRow}:G{currentRow}");
        range2.Merge().Value = "营销订单发货联系单";
        range2.Style.Font.Bold = true;
        range2.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        range2.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        range2.Style.Font.FontSize += 3;
        range2.Style.Alignment.WrapText = true;
        range2.Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"H{currentRow}").SetValue("订单号：");
        worksheet.Cell($"I{currentRow}").SetValue(headerData[0]); // 订单号
        worksheet.Cell($"I{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"I{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"I{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"I{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        currentRow++;

        // 填充 3 - AH 单元格（实际是 A 到 H 列）
        worksheet.Range($"A{currentRow}:G{currentRow}").Merge().Value = "项目名称：";
        worksheet.Range($"A{currentRow}:G{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Range($"A{currentRow}:G{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Range($"A{currentRow}:G{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Range($"A{currentRow}:G{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Range($"B{currentRow}:G{currentRow}").Merge().Value = headerData[1]; // 项目名称
        worksheet.Range($"B{currentRow}:G{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Range($"B{currentRow}:G{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Range($"B{currentRow}:G{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Range($"B{currentRow}:G{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"H{currentRow}").SetValue("业务日期：");
        worksheet.Cell($"I{currentRow}").SetValue(headerData[2]); // 业务日期
        worksheet.Cell($"I{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"I{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"I{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"I{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        currentRow++;

        // 填充 4 行
        worksheet.Cell($"A{currentRow}").SetValue("业务部门:");
        worksheet.Cell($"A{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"A{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Range($"B{currentRow}:D{currentRow}").Merge().Value = headerData[3]; // 业务部门
        worksheet.Range($"B{currentRow}:D{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Range($"B{currentRow}:D{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Range($"B{currentRow}:D{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Range($"B{currentRow}:D{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"E{currentRow}").SetValue("业务员:");
        worksheet.Cell($"E{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"E{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"E{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"E{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Range($"F{currentRow}:G{currentRow}").Merge().Value = headerData[4]; // 业务员
        worksheet.Range($"F{currentRow}:G{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Range($"F{currentRow}:G{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Range($"F{currentRow}:G{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Range($"F{currentRow}:G{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"H{currentRow}").SetValue("发货日期:");
        worksheet.Cell($"H{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"H{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"H{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"H{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"I{currentRow}").SetValue(headerData[5]); // 发货日期
        worksheet.Cell($"I{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"I{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"I{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"I{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        currentRow++;

        // 填充 5 行
        worksheet.Cell($"A{currentRow}").SetValue("需方单位:");
        worksheet.Cell($"A{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"A{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Range($"B{currentRow}:I{currentRow}").Merge().Value = headerData[6]; // 需方单位
        worksheet.Range($"B{currentRow}:I{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Range($"B{currentRow}:I{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Range($"B{currentRow}:I{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Range($"B{currentRow}:I{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        currentRow++;

        worksheet.Cell($"A{currentRow}").SetValue("打包要求:");
        worksheet.Cell($"A{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"A{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Range($"B{currentRow}:I{currentRow}").Merge().Value = packingRequirement;
        worksheet.Range($"B{currentRow}:I{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Range($"B{currentRow}:I{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Range($"B{currentRow}:I{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Range($"B{currentRow}:I{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        currentRow++;

        // 填充 6 行
        worksheet.Cell($"A{currentRow}").SetValue("外部合同号：");
        worksheet.Cell($"A{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"A{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Range($"B{currentRow}:E{currentRow}").Merge().Value = headerData[7]; // 外部合同号
        worksheet.Range($"B{currentRow}:E{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Range($"B{currentRow}:E{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Range($"B{currentRow}:E{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Range($"B{currentRow}:E{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"F{currentRow}").SetValue("内部合同号：");
        worksheet.Cell($"F{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"F{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"F{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"F{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Range($"G{currentRow}:I{currentRow}").Merge().Value = headerData[8]; // 内部合同号
        worksheet.Range($"G{currentRow}:I{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Range($"G{currentRow}:I{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Range($"G{currentRow}:I{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Range($"G{currentRow}:I{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        currentRow++;

        // 填充 7 行
        worksheet.Cell($"A{currentRow}").SetValue("标准号");
        worksheet.Cell($"A{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"A{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"A{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"B{currentRow}").SetValue("产品名称（规格、材质、性能等级）");
        worksheet.Cell($"B{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"B{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"B{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"B{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"C{currentRow}").SetValue("数量");
        worksheet.Cell($"C{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"C{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"C{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"C{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"D{currentRow}").SetValue("含税单价");
        worksheet.Cell($"D{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"D{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"D{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"D{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"E{currentRow}").SetValue("含税金额");
        worksheet.Cell($"E{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"E{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"E{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"E{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"F{currentRow}").SetValue("工程名称");
        worksheet.Cell($"F{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"F{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"F{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"F{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"G{currentRow}").SetValue("批次");
        worksheet.Cell($"G{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"G{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"G{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"G{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"H{currentRow}").SetValue("备注");
        worksheet.Cell($"H{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"H{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"H{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"H{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        worksheet.Cell($"I{currentRow}").SetValue("包    装");
        worksheet.Cell($"I{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell($"I{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell($"I{currentRow}").Style.Alignment.WrapText = true;
        worksheet.Cell($"I{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        currentRow++;
    }

    // 填充产品数据
    private void FillProductData(List<string[]> productData, ref double totalNum, ref double totalPrice)
    {
        int startRow = currentRow;
        double tn = 0;
        double tp = 0;
        foreach (var product in productData)
        {
            for (int i = 0; i < product.Length; i++)
            {

                if (i == 2)
                {
                    double vc = double.Parse(product[i]);

                    tn += vc;
                }
                if (i == 4)
                {
                    double vp = double.Parse(product[i]);
                    tp += vp;
                }
                worksheet.Cell(currentRow, i + 1).Value = product[i];
                worksheet.Cell(currentRow, i + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                worksheet.Cell(currentRow, i + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                worksheet.Cell(currentRow, i + 1).Style.Alignment.WrapText = true;
                worksheet.Cell(currentRow, i + 1).Style.Alignment.SetIndent(0); // 设置缩进为 0
            }
            currentRow++;
        }

        // 填充合计行
        worksheet.Cell(currentRow, 1).Value = "合计";
        worksheet.Cell(currentRow, 3).Value = tn;
        worksheet.Cell(currentRow, 5).Value = tp;
        worksheet.Cell(currentRow, 6).Value = "合计数量:";
        totalNum += tn;
        totalPrice += tp;
        worksheet.Cell(currentRow, 7).Value = totalNum;
        worksheet.Cell(currentRow, 8).Value = "合计金额:";
        worksheet.Cell(currentRow, 9).Value = totalPrice;
        worksheet.Cell(currentRow, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        worksheet.Cell(currentRow, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        worksheet.Cell(currentRow, 1).Style.Alignment.WrapText = true;
        worksheet.Cell(currentRow, 1).Style.Alignment.SetIndent(0); // 设置缩进为 0
        currentRow++;

        //// 填充编制、审核、审批行
        //worksheet.Range($"A{currentRow}:I{currentRow}").Merge().Value = "编制：                                                    审核：                                               审批（仅指出口订单）：";
        //worksheet.Range($"A{currentRow}:I{currentRow}").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        //worksheet.Range($"A{currentRow}:I{currentRow}").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        //worksheet.Range($"A{currentRow}:I{currentRow}").Style.Alignment.WrapText = true;
        //worksheet.Range($"A{currentRow}:I{currentRow}").Style.Alignment.SetIndent(0); // 设置缩进为 0
        //currentRow++;

        // 为表格添加黑边，调整范围不包含空格行
        var borderRange = worksheet.Range($"A{startRow - 7}:I{currentRow - 2}");
        borderRange.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
        borderRange.Style.Border.OutsideBorderColor = XLColor.Black;
        borderRange.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
        borderRange.Style.Border.InsideBorderColor = XLColor.Black;

        // 空两行
        currentRow += 2;
    }

    // 生成多个联系单
    public void GenerateContactSheets(List<string[]> headerDataList, List<string[]> allProductData, string packingRequirement, ref double totalNum, ref double totalPrice, int numPerPage)
    {
        for (int i = 0; i < allProductData.Count; i += numPerPage)
        {
            GenerateHeader(headerDataList[i / numPerPage], packingRequirement);
            List<string[]> currentProductData = allProductData.GetRange(i, Math.Min(numPerPage, allProductData.Count - i));
            FillProductData(currentProductData, ref totalNum, ref totalPrice);
        }
    }

    // 保存工作簿
    public void SaveWorkbook(string filePath)
    {
        workbook.SaveAs(filePath);
        Console.WriteLine("Excel 文件已生成。");
    }
}

class Program
{
    static void Main()
    {
        // 从数据库查询数据
        var (list1, list2, firstRemark) = DatabaseQuery.QueryData();
        double totalNum = 0;
        double totalPrice = 0;

        // 处理 list2 中的标准号
        foreach (var row in list2)
        {
            if (row.Length > 1)
            {
                string productName = row[1];
                if (productName.Contains("大六角螺栓") && productName.Contains("10.9级"))
                {
                    row[0] = "GB/T1228-1231-2006";
                }
                else if (productName.Contains("扭剪螺栓") && productName.Contains("10.9级"))
                {
                    row[0] = "GB/T3632-33-2008";
                }
                else if (productName.Contains("圆柱头焊钉"))
                {
                    row[0] = "GB/T10433-2002";
                }
            }
        }

        ContactSheetGenerator generator = new ContactSheetGenerator();
        int numPerPage = 0;
        string dingdan = "";
        try
        {
            string content = File.ReadAllText("./param/meiyeshuliang.txt");
            if (int.TryParse(content, out int a))
            {
                numPerPage = a;
            }
            else
            {
                Console.WriteLine("每页显示条数文件内容无法转换为整数。");
            }

            string orderNumbers = File.ReadAllText("./param/dingdan.txt");
            dingdan = orderNumbers;


        }
        catch (FileNotFoundException)
        {
            Console.WriteLine("错误: 文件未找到。");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"发生未知错误: {ex.Message}");
        }

        generator.GenerateContactSheets(list1, list2, firstRemark, ref totalNum, ref totalPrice, numPerPage);
        generator.SaveWorkbook("工作联系单-" + dingdan + ".xlsx");
    }
}
}